﻿package Kerjin.Util.Google;

import java.io.BufferedReader;
import java.io.DataOutputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.Hashtable;
import java.util.Iterator;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.methods.HttpUriRequest;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;

public class FusionTablesClient 
{
	private String _Message = "";//訊息
	private String _Auth = null;//認證資訊

	//建構子
	public FusionTablesClient()
	{
	}
	
	// 物件傳遞給使用者的訊息
	public String getMessage()
	{
		return _Message;
	}
	
	// 設定已取得的認證
	public void setAuth(String auth)
	{
		_Auth = auth;
		_Message = "Auth: " + _Auth;
	}
	
	// ClientLogin 取得存取Google Account帳戶的權限
	public boolean GetAuth(String account, String password)
	{
		_Message = "";
		try
		{
			// 對API提出需求並取得回應
			URL url = new URL("https://www.google.com/accounts/ClientLogin?accountType=GOOGLE&Email=" + URLEncoder.encode(account) + "&Passwd=" + URLEncoder.encode(password) + "&service=fusiontables&source=TheDataStewards-DataSyncr-1.05");
            HttpURLConnection uc = (HttpURLConnection) url.openConnection();
            uc.setRequestProperty("Content-Type","application/x-www-form-urlencoded");
            uc.setRequestMethod("GET");
            uc.setReadTimeout(30000);
            uc.connect();

            if(uc.getResponseCode() == 200)
            {
                InputStream is = uc.getInputStream();
                BufferedReader br = new BufferedReader(new InputStreamReader(is, "UTF-8"));
                
                String strLineData;
                while ((strLineData = br.readLine()) != null) 
                {
                	final String[] aryData = strLineData.split("=");
                	if ("Auth".equals(aryData[0]))
        			{
        				_Auth = aryData[1];
        				break;
        			}
                }
                br.close();
                if (_Auth != null)
                {
                	_Message = "成功取得認證。";
                	return true;
                }
                else
                {
                	throw new Exception("取得認證失敗。");
                }
            }
            else
            {
            	throw new Exception("連線失敗(" + uc.getResponseCode() + ")。");
            }
		}
		catch (Exception exp)
		{
			_Message = "取得認證失敗(" + exp.getMessage() + ")。";
			return false;
		}
	}

	// 建立資料表
	@SuppressWarnings("unused")
	public String CreateTable(String tableName, Hashtable fieldInfo)
	{
		_Message = "";
		String strTableID = "";
		try
		{
			StringBuffer sbField = new StringBuffer(256);
			Iterator it = fieldInfo.keySet().iterator();
			Object nvFirst = it.next();
			sbField.append(nvFirst.toString() + ":" + fieldInfo.get(nvFirst).toString());
			while(it.hasNext()) 
			{ 
				sbField.append(",");
				Object key = it.next();
				sbField.append(key.toString() + ":" + fieldInfo.get(key).toString());
			}
			
			String strSql = "CREATE TABLE '" + tableName + "' (" + sbField.toString() + ")";
			sbField = null;
			String strPostValue = "sql=" + URLEncoder.encode(strSql);
			strSql = null;
			
			//對API提出需求並取得回應
            URL url = new URL("https://www.google.com/fusiontables/api/query");
            HttpURLConnection uc = (HttpURLConnection) url.openConnection();
            uc.setRequestProperty("Content-Type","application/x-www-form-urlencoded");
            uc.setRequestProperty("Authorization", "GoogleLogin auth=" + _Auth);
            uc.setRequestProperty("Content-Length", String.valueOf(strPostValue.getBytes().length));
            uc.setRequestMethod("POST");
            uc.setDoOutput(true); 
            uc.setDoInput(true);
            uc.setReadTimeout(30000);
            uc.connect();
            
            DataOutputStream dos = new java.io.DataOutputStream(uc.getOutputStream()); 
            dos.writeBytes(strPostValue);
            
            if(uc.getResponseCode() == 200)
            {
                InputStream is = uc.getInputStream();
                BufferedReader br = new BufferedReader(new InputStreamReader(is, "UTF-8"));
                
                StringBuffer sbMessage = new StringBuffer();
                
                String strLineData;
                int intLineCount = 0;
                while ((strLineData = br.readLine()) != null) 
                {
                	sbMessage.append(strLineData);
                	intLineCount++;
                	if (intLineCount == 2)
                	{
                		strTableID = strLineData;
                		break;
                	}
                }
                br.close();
                
                if (strTableID != "")
                {
                	_Message = "已成功建立資料表, 資料表編號為 " + strTableID + "。";
                	return strTableID;
                }
                else
                {
                	throw new Exception(sbMessage.toString());
                }
            }
            else
            {
            	throw new Exception("連線失敗(" + uc.getResponseCode() + ")。");
            }
		}
		catch (Exception exp)
		{
			_Message = "建立資料表失敗(" + exp.getMessage() + ")。";
		}
		return strTableID;
	}
	
	// 刪除資料表
	public boolean DropTable(String tableID)
	{
		_Message = "";
		try
		{
			String strPostValue = "sql=" + URLEncoder.encode("DROP TABLE " + tableID);
			
			//對API提出需求並取得回應
			URL url = new URL("https://www.google.com/fusiontables/api/query");
            HttpURLConnection uc = (HttpURLConnection) url.openConnection();
            uc.setRequestProperty("Content-Type","application/x-www-form-urlencoded");
            uc.setRequestProperty("Authorization", "GoogleLogin auth=" + _Auth);
            uc.setRequestProperty("Content-Length", String.valueOf(strPostValue.getBytes().length));
            uc.setRequestMethod("POST");
            uc.setDoOutput(true); 
            uc.setDoInput(true);
            uc.setReadTimeout(30000);
            uc.connect();
            
            DataOutputStream dos = new DataOutputStream(uc.getOutputStream()); 
            dos.writeBytes(strPostValue);
            
            if(uc.getResponseCode() == 200)
            {
                InputStream is = uc.getInputStream();
                BufferedReader br = new BufferedReader(new InputStreamReader(is, "UTF-8"));
                
                boolean blnResult = false; 
                String strLineData;
                while ((strLineData = br.readLine()) != null) 
                {
                	if (strLineData.equals("OK"))
					{
                		blnResult = true;
                		_Message = "已成功刪除資料表(" + tableID + ")。";
						return true;
					}
                }
                br.close();
                
                if (blnResult)
				{
            		blnResult = true;
            		_Message = "已成功刪除資料表(" + tableID + ")。";
					return true;
				}
				else
				{
					throw new Exception(strLineData);
				}
            }
            else
            {
            	throw new Exception("連線失敗(" + uc.getResponseCode() + ")。");
            }
		}
		catch (Exception exp)
		{
			_Message = "刪除資料表失敗(" + exp.getMessage() + ")。";
			return false;
		}
	}

	// 取得資料表機碼
	public String GetTableShema(String tableID)
	{
		_Message = "";
		StringBuffer sbMessage = new StringBuffer();
		try
		{
			// 對API提出需求並取得回應
			URL url = new URL("https://www.google.com/fusiontables/api/query?sql=DESCRIBE+" + tableID);
            HttpURLConnection uc = (HttpURLConnection) url.openConnection();
            uc.setRequestProperty("Content-Type","application/x-www-form-urlencoded");
            uc.setRequestProperty("Authorization", "GoogleLogin auth=" + _Auth);
            uc.setRequestMethod("GET");
            uc.setReadTimeout(30000);
            uc.connect();

            if(uc.getResponseCode() == 200)
            {
                InputStream is = uc.getInputStream();
                BufferedReader br = new BufferedReader(new InputStreamReader(is, "UTF-8"));
                
                String strLineData;
                while ((strLineData = br.readLine()) != null) 
                {
                	sbMessage.append(strLineData);
                }
                br.close();

                _Message = "已成功取得資料表機碼。";
            }
            else
            {
            	throw new Exception("連線失敗(" + uc.getResponseCode() + ")。");
            }
		}
		catch (Exception exp)
		{
			_Message = "取得資料表機碼失敗(" + exp.getMessage() + ")。";
		}
		return sbMessage.toString();
	}
	
	// 取得資料表清單
	public String GetTableList()
	{
		_Message = "";
		StringBuffer sbMessage = new StringBuffer();
		try
		{
			// 對API提出需求並取得回應
			URL url = new URL("https://www.google.com/fusiontables/api/query?sql=SHOW+TABLES&encid=false");
            HttpURLConnection uc = (HttpURLConnection) url.openConnection();
            uc.setRequestProperty("Content-Type","application/x-www-form-urlencoded");
            uc.setRequestProperty("Authorization", "GoogleLogin auth=" + _Auth);
            uc.setRequestMethod("GET");
            uc.setReadTimeout(30000);
            uc.connect();

            if(uc.getResponseCode() == 200)
            {
                InputStream is = uc.getInputStream();
                BufferedReader br = new BufferedReader(new InputStreamReader(is, "UTF-8"));
                
                String strLineData;
                while ((strLineData = br.readLine()) != null) 
                {
                	sbMessage.append(strLineData);
                }
                br.close();

                _Message = "已成功取得資料表清單。";
            }
            else
            {
            	throw new Exception("連線失敗(" + uc.getResponseCode() + ")。");
            }
		}
		catch (Exception exp)
		{
			_Message = "取得資料表清單失敗(" + exp.getMessage() + ")。";
		}
		return sbMessage.toString();
	}

	public String ExecuteQuery(String sql)
	{
		_Message = "";
		StringBuffer sbMessage = new StringBuffer();
		try
		{
			String strPostValue = "sql=" + URLEncoder.encode(sql);
			
			//對API提出需求並取得回應
			URL url = new URL("https://www.google.com/fusiontables/api/query");
            HttpURLConnection uc = (HttpURLConnection) url.openConnection();
            uc.setRequestProperty("Content-Type","application/x-www-form-urlencoded");
            uc.setRequestProperty("Authorization", "GoogleLogin auth=" + _Auth);
            uc.setRequestProperty("Content-Length", String.valueOf(strPostValue.getBytes().length));
            uc.setRequestMethod("POST");
            uc.setDoOutput(true); 
            uc.setDoInput(true);
            uc.setReadTimeout(30000);
            uc.connect();
            
            DataOutputStream dos = new DataOutputStream(uc.getOutputStream()); 
            dos.writeBytes(strPostValue);
            
            if(uc.getResponseCode() == 200)
            {
                InputStream is = uc.getInputStream();
                BufferedReader br = new BufferedReader(new InputStreamReader(is, "UTF-8"));
                
                String strLineData;
                while ((strLineData = br.readLine()) != null) 
                {
                	sbMessage.append(strLineData);
                }
                br.close();
                _Message = "已成功執行敘述。";
            }
            else
            {
            	throw new Exception("連線失敗(" + uc.getResponseCode() + ")。");
            }
		}
		catch (Exception exp)
		{
			_Message = "執行敘述失敗(" + exp.getMessage() + ")。\n";
		}
		return sbMessage.toString();
	}
}
